package com.sg.tr.web;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Formatter;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.druid.sql.ast.SQLObject;
import com.alibaba.fastjson.JSONArray;
import com.sg.common.empty.core.ConditionRes;
import com.sg.common.empty.core.Conditions2;
import com.sg.common.empty.core.FilterVal;
import com.sg.common.empty.core.Params2;
import com.sg.common.empty.service.CfgService;
import com.sg.common.utils.CollUtil;
import com.sg.common.utils.Constant;
import com.sg.common.utils.DateTimeUtils;
import com.sg.tr.service.TrApiService;

@Repository
@Controller
@RequestMapping(value = "/api")
public class TrApiController {

	private Logger logger = Logger.getLogger(getClass());

	@Autowired
	CfgService service;

	@Autowired
	TrApiService taService;

	@RequestMapping(value = "/tr/plan/list")
	@ResponseBody
	public Object findPlanList(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {
		long s = System.currentTimeMillis();
		String route = (String) request.getAttribute("conf.route");
		// TODO 如果有js，使用js确定使用哪个sql
		Conditions2 condis = Conditions2.init(request, requestBody);
		HttpSession session = request.getSession();
		List<String> myRoles = (List<String>) session
				.getAttribute(Constant.SESSION_MYROLES_CODE);
		Boolean canSeeAll = false;
		if (CollUtil.isNotEmpty(myRoles)) {
			for (String myRole : myRoles) {
				String TRI001 = "TRI001";		//TRI001,企划部综合管理员与人教部教培科人员,可以看到所有部门中心培训计划/实际
				if (StringUtils.equals(myRole, TRI001)) {
					canSeeAll = true;
					break;
				}
			}
		}
		if (canSeeAll) {
			session.setAttribute(Constant.SESSION_MYDEPORG, null);
		}

		ConditionRes cr = condis.buildCon();
		String sorts = condis.buildSorts();
		String limit = condis.buildLimit();

		// 替换
		String orancsql = Constant.get(route + ".csql");
		String oransql = Constant.get(route + ".sql");
		orancsql = orancsql.replace("@conditions", cr.getSql()).replace(
				"@orderby", sorts);
		oransql = oransql.replace("@conditions", cr.getSql())
				.replace("@orderby", sorts).replace("@limit", limit);

		List<Object> countList = service.queryBySql(orancsql, cr.getParams());
		String res = Constant.get(route + ".res");
		List<Map<String, Object>> dataList = service.queryBySqlToMap(oransql,
				cr.getParams(), res.split(","));
		
		List<Map<String, Object>> planList = new ArrayList<Map<String, Object>>();
		
		String pcreSql = "SELECT tr_pcre.id, tr_pcre.poca, mdm_poca.tyds, tr_pcre.clst, tr_clst. LEVEL, CONCAT( tr_clst.clds, '/', tr_clst.thpr ), tr_pcre. HOUR, tr_pcre. ENABLE FROM tr_pcre LEFT JOIN tr_clst ON tr_pcre.clst = tr_clst.id LEFT JOIN mdm_poca ON tr_pcre.poca = mdm_poca.id WHERE tr_pcre. ENABLE <> '' and tr_pcre.plan =?";
		String pcreRes = "id,poca,tyds,clst,level,clds,hour,enable";
		for(Map<String, Object> listMap: dataList){
			Map<String, Object> plan = new HashMap<String, Object>();
			String id = (String)listMap.get("id");
			List<Map<String, Object>> pcreList = service.queryBySqlToMap(pcreSql, new Object[]{id}, pcreRes.split(","));
			plan.putAll(listMap);
			plan.put("pcreList", pcreList);
			planList.add(plan);
		}

		int count = ((BigInteger) countList.get(0)).intValue();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("items", planList);
		map.put("total", count);
		map.put("code", 2000);
		long e = System.currentTimeMillis();
		System.out.println(e - s);
		return map;
	}
	
	@RequestMapping(value = "/tr/actual/list")
	@ResponseBody
	public Object findActualList(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {
		long s = System.currentTimeMillis();
		String route = (String) request.getAttribute("conf.route");
		// TODO 如果有js，使用js确定使用哪个sql
		Conditions2 condis = Conditions2.init(request, requestBody);
		HttpSession session = request.getSession();
		List<String> myRoles = (List<String>) session
				.getAttribute(Constant.SESSION_MYROLES_CODE);
		Boolean canSeeAll = false;
		if (CollUtil.isNotEmpty(myRoles)) {
			for (String myRole : myRoles) {
				String TRI001 = "TRI001";
				if (StringUtils.equals(myRole, TRI001)) {
					canSeeAll = true;
					break;
				}
			}
		}
		if (canSeeAll) {
			session.setAttribute(Constant.SESSION_MYDEPORG, null);
		}

		ConditionRes cr = condis.buildCon();
		String sorts = condis.buildSorts();
		String limit = condis.buildLimit();

		// 替换
		String orancsql = Constant.get(route + ".csql");
		String oransql = Constant.get(route + ".sql");
		orancsql = orancsql.replace("@conditions", cr.getSql()).replace(
				"@orderby", sorts);
		oransql = oransql.replace("@conditions", cr.getSql())
				.replace("@orderby", sorts).replace("@limit", limit);

		List<Object> countList = service.queryBySql(orancsql, cr.getParams());
		String res = Constant.get(route + ".res");
		List<Map<String, Object>> dataList = service.queryBySqlToMap(oransql,
				cr.getParams(), res.split(","));
		List<Map<String, Object>> actualList = new ArrayList<Map<String, Object>>();
	
		String issueSql = "select id,type,content,enable from tr_issue where actual=? and enable='1'";
		String issueRes = "id,type,content,enable";
		String acreSql = "SELECT tr_acre.id, tr_acre.poca, mdm_poca.tyds,tr_acre.clst, tr_clst.level, CONCAT(tr_clst.clds,'/',tr_clst.thpr), tr_acre.hour, tr_acre.enable FROM tr_acre LEFT JOIN tr_clst ON tr_acre.clst = tr_clst.id LEFT JOIN mdm_poca ON tr_acre.poca = mdm_poca.id WHERE tr_acre.enable<>'' and tr_acre.actual =?";
		String acreRes = "id,poca,tyds,clst,level,clds,hour,enable";
		for(Map<String, Object> listMap: dataList){
			Map<String, Object> actual = new HashMap<String, Object>();
			String id = (String)listMap.get("id");
			List<Map<String, Object>> issueList = service.queryBySqlToMap(issueSql, new Object[]{id}, issueRes.split(","));
			List<Map<String, Object>> acreList = service.queryBySqlToMap(acreSql, new Object[]{id}, acreRes.split(","));
			actual.putAll(listMap);
			actual.put("issueList", issueList);
			actual.put("acreList", acreList);
			actualList.add(actual);
		}
		
		int count = ((BigInteger) countList.get(0)).intValue();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("items", actualList);
//		map.put("items", dataList);
		map.put("total", count);
		map.put("code", 2000);
		long e = System.currentTimeMillis();
		System.out.println(e - s);
		return map;
	}
	
	/**
	 *  培训计划审核
	 * @param request
	 * @param requestBody
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/tr/plan/check")
	@ResponseBody
	public Object check(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {

		String checkSql = "update tr_plan set enable='2',checkMember=?,checkDate=? where id=?";
		String currentMemberId = (String) FilterVal.init("currentMemberId",
				service).get2();
		String currentTime = (String) FilterVal.init("currentTime", service)
				.get2();
//		String depId = (String) FilterVal.init("depId", service).get2();
		// String id = Constant.getRbString(requestBody,"id");
		// List<Map<String, Object>> planList = null;
		String ids = requestBody.get("ids").toString();
		String res = taService.autoCheck(checkSql, currentMemberId,
				currentTime, ids);

		Map<String, Object> map = new HashMap<String, Object>();
		if (StringUtils.equals(res, "success")) {
			map.put("code", 2000);
			map.put("currentMemberId", currentMemberId);
			map.put("currentTime", currentTime);
		} else {
			map.put("code", 2500);
			map.put("msg", "失败,请确认计划状态");
		}

		return map;
	}

	
	/**
	 * 培训实际完成/取消,编辑
	 * @param request
	 * @param requestBody
	 * @return
	 * @throws Exception
	 */
//	2018-8-22 取消培训实际的单独更新(取消后台自动生成培训人次信息及培训人次是否生成属性后,培训实际的update使用通用即可)
//	2018-8-27 增加update方法:①培训实际中增加“培训问题”,以列表方式记录;②培训实际的状态变更时,后台需要更新该状态至培训计划表中.
	@RequestMapping(value = "/tr/actual/update")
	@ResponseBody
	public Object updateActual(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception{
		List<Map<String, Object>> idsMap = taService.updateSer(request,requestBody);
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("code", 2000);
		map.put("ids", idsMap);
		/*String res = taService.actuaUp(oransql, sqlParams);
		if (StringUtils.equals(res, "success")) {
			map.put("code", 2000);
		} else {
			map.put("code", 2500);
			map.put("msg", "失败");
		}*/
		return map;
	}
	
	/**
	 * 培训计划新增页面，选定岗位xingzhi后，过滤对应的等级（初级、中级、高级等）
	 * @param request
	 * @param requestBody
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/tr/clstLevel/select")
	@ResponseBody
	public Object findClstLevel(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {
		String poca = request.getParameter("poca");
//		String clstLevelSql = "SELECT  DISTINCT cfg_post.descr,tr_clst.`level` from cfg_post LEFT JOIN tr_clst on cfg_post.poca=tr_clst.poca where cfg_post.id =?";
		String clstLevelSql = "SELECT DISTINCT tyds,level from tr_clst where poca =?";
		String clstLevelRes = "descr,level";
		List<Map<String, Object>> dataList = service.queryBySqlToMap(clstLevelSql,
				new Object[]{poca}, clstLevelRes.split(","));
		List<Map<String, Object>> clstLevels = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> map:dataList){
			Map<String, Object> mapLevel = new HashMap<String, Object>();
			mapLevel.put("value", map.get("level"));
			mapLevel.put("label", map.get("level"));
			clstLevels.add(mapLevel);
		}
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("items", clstLevels);
		map.put("code", 2000);
		return map;
	}
	
	/**
	 * 选定岗位性质及等级后，过滤课程列表
	 * @param request
	 * @param requestBody
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value = "/tr/clst/list")
	@ResponseBody
	public Object findClstList(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {
		
		String poca = request.getParameter("poca");
		String level = request.getParameter("level");
		level = java.net.URLDecoder.decode(level,"UTF-8");
		
		String clstSql = "SELECT tr_clst.id,tr_clst.`level`,tr_clst.clds,tr_clst.thpr,tr_clst.hour from tr_clst where tr_clst.poca=? and tr_clst.level=? and tr_clst.enable='1'";
		String clstRes = "id,level,clds,thpr,hour";
		List<Map<String, Object>> dataList = service.queryBySqlToMap(clstSql,
				new Object[]{poca, level}, clstRes.split(","));
		List<Map<String, Object>> clsts = new ArrayList<Map<String, Object>>();
		for(Map<String, Object> map:dataList){
			Map<String, Object> mapLevel = new HashMap<String, Object>();
			mapLevel.put("value", map.get("id"));
			mapLevel.put("label", map.get("clds").toString() + "/" + map.get("thpr").toString());
			clsts.add(mapLevel);
		}
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("items", clsts);
		map.put("code", 2000);
		return map;

	}
	
	@RequestMapping(value = "/tr/plan/add")
	@ResponseBody
	public Object addPlan(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception{
		String route = (String) request.getAttribute("conf.route");

		String oransql = Constant.get(route + ".sql");
		List<String> sqlParams = Params2.init(request,requestBody,service).build();
		service.excuteBySql(oransql, sqlParams.toArray());
		
		Object id = sqlParams.get(0);
		Object currentTime =  sqlParams.get(30);
		Object currentMember =  sqlParams.get(29);
		List<Map<String, Object>> idsMap = taService.updatePcreList(request, requestBody, id, currentTime, currentMember);

		Map<String, Object> map = new HashMap<String, Object>();
		Map<String, Object> item = new HashMap<String, Object>();
		item.put("id", sqlParams.get(0));
		map.put("item", item);
		map.put("code", 2000);
		map.put("ids", idsMap);
		return map;
	}
	
	@RequestMapping(value = "/tr/plan/update")
	@ResponseBody
	public Object updatePlan(HttpServletRequest request,@RequestBody Map<String, Object> requestBody) {
		String route = (String) request.getAttribute("conf.route");
		String oransql = Constant.get(route + ".sql");
		List<String> sqlParams = Params2.init(request,requestBody,service).build();
		Map<String, Object> map = new HashMap<String, Object>();
		try {
			service.excuteBySql(oransql, sqlParams.toArray());
			
			Object id = sqlParams.get(29);
			Object currentTime =  sqlParams.get(28);
			Object currentMember =  sqlParams.get(27);
			List<Map<String, Object>> idsMap = taService.updatePcreList(request, requestBody, id, currentTime, currentMember);
			map.put("ids", idsMap);
			map.put("code", 2000);
		} catch (Exception e) {
			logger.error(e.getMessage());
			map.put("code", 5030);
			map.put("msg", "更新发生内部错误，请联系管理员");
		}
		return map;
	}
	
	
	@RequestMapping(value = "/tr/score/insert")
	@ResponseBody
	public Object insertScore(HttpServletRequest request, @RequestBody Map<String, Object> requestBody) throws Exception{
		String actual = requestBody.get("id").toString();
		ArrayList scoreList = (ArrayList)requestBody.get("tableData");
		for(int i = 0; i<scoreList.size(); i++){
			Map<String, Object> scoreRec = (Map<String, Object>)scoreList.get(i);
			String epnum = scoreRec.get("num").toString();
//			String name = scoreRec.get("name").toString();
			String score = scoreRec.get("score").toString();
			if(StringUtils.isNotBlank(score)){
				String sql = "update tr_personal set score=? where actual=? and epnum=? ";
				service.excuteBySql(sql, new Object[]{score, actual, epnum});
			}
		}
		String upSql = "update tr_actual set scoreFinished ='1' where id=?";
		service.excuteBySql(upSql, new Object[]{actual});
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("code", 2000);
		
		return map;
	}
	
	
	@RequestMapping(value = "/tr/score/list")
	@ResponseBody
	public Object findScoreList(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {
		
		String actual = request.getParameter("actual");
		
		String qSql = "SELECT DISTINCT epnum,name,score from tr_personal where ENABLE='1' and actual=?";
		String qRes = "num,name,score";
		List<Map<String, Object>> dataList = service.queryBySqlToMap(qSql,
				new Object[]{actual}, qRes.split(","));
		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("items", dataList);
		map.put("code", 2000);
		return map;

	}
	
//	@RequestMapping(value = "/tr/resum/list")			//tr_resum相关内容(未测试未部署)
//	@ResponseBody
	public Object findResumList(HttpServletRequest request,
			@RequestBody Map<String, Object> requestBody) throws Exception {
//		String number = request.getParameter("number");
		String eventId = requestBody.get("eventId").toString();
		String orancsql = "select count(*)  from tr_resum tr where tr.enable='1' and tr.num=?";
		String oransql="select tr.id,tr.num,tr.ordInd,tr.descr,tr.value,tr.unit,tr.remark from tr_resum tr where tr.enable='1' and tr.num=?";
		String res = "id,num,ordInd,descr,value,unit,remark";
		List<Object> countList = service.queryBySql(orancsql, new Object[]{eventId});
		List<Map<String, Object>> dataList = service.queryBySqlToMap(oransql,
				new Object[]{eventId}, res.split(","));

		int count = ((BigInteger) countList.get(0)).intValue();
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("items", dataList);
		map.put("total", count);
		map.put("code", 2000);
		return map;
	}
	
	
/**
 * 	各部门中心培训基本情况柱状图(培训总课时、培训总次数、培训总人数、培训总人时数/各中心)
 * @param request
 * @param requestBody
 * @return
 */
	@RequestMapping(value = "/tr/actual/histogram")
	@ResponseBody
	public Object histogram(HttpServletRequest request,@RequestBody Map<String, Object> requestBody) {

		String[] columns =  new String[]{"部门中心","培训总课时","培训总次数","培训总人数","培训总人时数"};
		String depCountSql = "SELECT tr_actual.depId, cfg_org.descr, sum(tr_actual. HOUR), count(*), sum( length(tr_actual.students) - length( REPLACE (tr_actual.students, ',', '')) + 1 ), sum(( length(tr_actual.students) - length( REPLACE (tr_actual.students, ',', '')) + 1 ) * tr_actual. HOUR ) FROM tr_actual LEFT JOIN cfg_org ON tr_actual.depId = cfg_org.id WHERE tr_actual. ENABLE ='1' AND tr_actual.depId <> '' GROUP BY tr_actual.depId";
		String depCountRes = "部门ID,部门中心,培训总课时,培训总次数,培训总人数,培训总人时数";
		List<Map<String, Object>> dataList = service.queryBySqlToMap(depCountSql, depCountRes.split(","));

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("columns", columns);
		map.put("item", dataList);
		map.put("code", 2000);
		return map;
	}
	
/**
 * 各中心培训人均课时折线图
 * @param request
 * @param requestBody
 * @return
 */
	@RequestMapping(value = "/tr/actual/line")
	@ResponseBody
	public Object line(HttpServletRequest request,@RequestBody Map<String, Object> requestBody) {

//		查询各部门中心的工班人员数量
 		String groupNumSql = "SELECT cfg_member.dept,cfg_org.descr,count(*) FROM `cfg_member` LEFT JOIN cfg_org on cfg_member.dept=cfg_org.id where cfg_member.delfg='0' and cfg_member.org<>'' and cfg_member.dept<>'' and cfg_member.org in (SELECT id from cfg_org where orglv='3' and delfg='0') GROUP BY cfg_member.dept";
 		String groupNumRes = "部门ID,部门中心,总人数";
 		List<Map<String, Object>> groupNumList = service.queryBySqlToMap(groupNumSql, groupNumRes.split(","));
 		
// 		折线图的columns属性,包括维度与数据列
 		List<String> columnsName =  new ArrayList<String>();
 		columnsName.add("月份");
 		for(Map<String, Object> groupNum : groupNumList){
				columnsName.add(groupNum.get("部门中心").toString());
			}
		
// 		列出目前所有的月份
 		String monthSql = "SELECT DISTINCT SUBSTR(tr_actual.date,1,7),enable from tr_actual where tr_actual.`enable`='1' ORDER BY SUBSTR(tr_actual.date,1,7)";
 		String monthRes = "月份,状态";
 		List<Map<String, Object>> monthList = service.queryBySqlToMap(monthSql, monthRes.split(","));
 		
 		List<Map<String, Object>> res = new ArrayList<Map<String, Object>>();
// 		逐月查询各中心的人均课时
 		for(Map<String, Object> month: monthList){
// 			查询各中心的总人时数
 			String depCountSql = "SELECT tr_actual.depId,SUBSTR(tr_actual.date,1,7),cfg_org.descr,sum((length(tr_actual.students)-length(REPLACE(tr_actual.students, ',', ''))+1) * tr_actual.HOUR) FROM tr_actual LEFT JOIN cfg_org ON tr_actual.depId = cfg_org.id WHERE tr_actual. ENABLE = '1' AND tr_actual.depId <> '' and tr_actual.date like ? GROUP BY tr_actual.depId,SUBSTR(tr_actual.date,1,7)";
 			String depCountRes = "部门ID,月份,部门中心,总人时数";
 			List<Map<String, Object>> dataList = service.queryBySqlToMap(depCountSql, new String[]{"%"+month.get("月份").toString()+"%"}, depCountRes.split(","));
 			
			Map<String, Object> sMonth = new HashMap<String, Object>();
			sMonth.put("月份",month.get("月份"));
//			查询各中心的平均课时（总人时数/工班总人数）
 			for(Map<String, Object> groupNum : groupNumList){
 	 			for(Map<String, Object> data: dataList){
 	 				if(StringUtils.equals(groupNum.get("部门ID").toString(),data.get("部门ID").toString())){
// 	 					该月份每个中心的平均值
 	 					sMonth.put(data.get("部门中心").toString(),String.format("%.2f",Double.valueOf((Double) data.get("总人时数"))/Integer.valueOf(groupNum.get("总人数").toString())));
 	 				}
 	 			}
 	 		}
 			res.add(sMonth);
 		}
 		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("columns", columnsName);
		map.put("item", res);
		map.put("code", 2000);
		return map;
	}
	
/**
 * 	分公司培训类别饼图(质量、安全、技术、管理、其他)
 * @param request
 * @param requestBody
 * @return
 */
	@RequestMapping(value = "/tr/actual/pie")
	@ResponseBody
	public Object pie(HttpServletRequest request,@RequestBody Map<String, Object> requestBody) {

		String[] columns =  new String[]{"培训类别","培训数量"};
 		
 		String cateNumSql = "SELECT REPLACE(REPLACE(REPLACE(replace(replace(cate,'0','质量'),'1','安全'),'2','技术'),'3','管理'),'4','其他'), count(*) from tr_actual where depId<>'' and `enable`='1' and cate<>'' GROUP BY cate";
 		String cateNumRes = "培训类别,培训数量";
 		List<Map<String, Object>> cateNumList = service.queryBySqlToMap(cateNumSql, cateNumRes.split(","));
 		
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("columns", columns);
		map.put("item", cateNumList);
		map.put("code", 2000);
		return map;
	}
	
	/**
	 * 培训计划的复制
	 */
//	
	/*@RequestMapping(value = "/tr/plan/copy")
	@ResponseBody
	public Object add(HttpServletRequest request,@RequestBody Map<String, Object> requestBody) {
		String route = (String) request.getAttribute("conf.route");

		String oransql = Constant.get(route + ".sql");
		List<String> sqlParams = Params2.init(request,requestBody,service).build();
		service.excuteBySql(oransql, sqlParams.toArray());

		Map<String, Object> map = new HashMap<String, Object>();
		Map<String, Object> item = new HashMap<String, Object>();
		item.put("id", sqlParams.get(0));
		map.put("item", item);
		map.put("code", 2000);
		return map;
	}*/

}
